using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Data.SqlTypes;
using InfraStructureLayer;
using System.Xml;


namespace DataAccessLayer
{
    public class DbConnector
    {
        public DbConnector()
        {
            _ConnectionString = System.Configuration.ConfigurationManager.AppSettings["SQLConnection"];
        }
        public DbConnector(string parConnectionString)
        {
            _ConnectionString = parConnectionString;
        }
        private DbProviderFactory insDbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        private string _ConnectionString;
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }
        private DbConnection _Connection = null;
        private DbTransaction _Transaction = null;

        public void OpenConnection()
        {
            if (_Connection == null)
            {
                _Connection = insDbProviderFactory.CreateConnection();
                _Connection.ConnectionString = _ConnectionString;
            }
            if (_Connection.State == ConnectionState.Closed)
            {
                try
                {
                    _Connection.Open();
                }
                catch (Exception ex)
                {
                    throw new SFDataException(GetErrorMessageMultilanguageString(2, new object[] { ex.Message }));
                }

            }
        }
        public void CloseConnecion()
        {
            if (_Transaction == null)
            {
                if (_Connection != null)
                {
                    try
                    {
                        _Connection.Close();
                    }
                    catch (Exception ex)
                    {

                        throw new SFDataException(GetErrorMessageMultilanguageString(3, new object[] { ex.Message }));
                    }

                    _Connection.Dispose();
                    _Connection = null;
                }
            }
        }

        public void BeginTransaction()
        {
            if (_Connection == null || _Connection.State == ConnectionState.Closed)
            {
                this.OpenConnection();
            }
            if (_Transaction == null)
            {
                try
                {
                    _Transaction = _Connection.BeginTransaction();
                }
                catch (Exception ex)
                {

                    throw new SFDataException(GetErrorMessageMultilanguageString(4, new object[] { ex.Message }));
                }

            }
            
        }
        public void CommitTransaction()
        {
            if (_Transaction != null)
            {
                try
                {
                    _Transaction.Commit();
                }
                catch (Exception ex)
                {

                    throw new SFDataException(GetErrorMessageMultilanguageString(5, new object[] { ex.Message }));
                }

                _Transaction.Dispose();
                _Transaction = null;
                this.CloseConnecion();
            }
        }
        public void RollbackTransaction()
        {
            if (_Transaction != null)
            {
                try
                {
                    _Transaction.Rollback();
                }
                catch (Exception ex)
                {

                    throw new SFDataException(GetErrorMessageMultilanguageString(6, new object[] { ex.Message }));
                }

                _Transaction.Dispose();
                _Transaction = null;
                this.CloseConnecion();
            }
        }

        public DbCommand CreateCommand(string parCommandText, DbParamCollection parDbParameters)
        {
            DbCommand insDbCommand = insDbProviderFactory.CreateCommand();
            insDbCommand.CommandText = parCommandText;
            insDbCommand.CommandType = CommandType.StoredProcedure;
            if (parDbParameters != null)
            {
                insDbCommand.Parameters.AddRange(parDbParameters.ToArray());
            }
            insDbCommand.Connection = _Connection;
            if (_Transaction != null)
            {
                insDbCommand.Transaction = _Transaction;
            }
            return insDbCommand;
        }
        public DataTable ExecuteDataTable(string parCommandText, DbParamCollection parDbParameters)
        {
           return ExecuteDataTable(parCommandText,parDbParameters,false); //
        }
        public DataTable ExecuteDataTable(string parCommandText)
        {
            DataTable insDataTable = new DataTable();
            this.OpenConnection();
            DbDataAdapter insDbDataAdapter = insDbProviderFactory.CreateDataAdapter();
            insDbDataAdapter.SelectCommand = CreateCommand(parCommandText, null);
            insDbDataAdapter.SelectCommand.CommandType = CommandType.Text;

            try
            {
                insDbDataAdapter.Fill(insDataTable);
            }
            catch (Exception ex)
            {
                this.CloseConnecion();
                throw new SFDataException(GetErrorMessageMultilanguageString(7, new object[] { parCommandText,ex.Message }));
            }
            
            this.CloseConnecion();
            return insDataTable;
        }
        
        public DataTable ExecuteDataTable(string parCommandText, DbParamCollection parDbParameters, bool parIsCached)
        {
                DataTable insDataTable = new DataTable();
                if (parIsCached)
                {
                    if (AppDomain.CurrentDomain.GetData(parCommandText) == null)
                    {
                        this.OpenConnection();
                        DbDataAdapter insDbDataAdapter = insDbProviderFactory.CreateDataAdapter();
                        insDbDataAdapter.SelectCommand = CreateCommand(parCommandText, parDbParameters);
                        
                        try
                        {
                            insDbDataAdapter.Fill(insDataTable);
                        }
                        catch (Exception ex)
                        {
                            this.CloseConnecion();
                            throw new SFDataException(GetErrorMessageMultilanguageString(7, new object[] { parCommandText, ex.Message }));
                        }

                        this.CloseConnecion();
                        AppDomain.CurrentDomain.SetData(parCommandText, insDataTable);
                    }
                    else
                    {
                        insDataTable = (DataTable)(AppDomain.CurrentDomain.GetData(parCommandText));
                    }
                }
                else
                {
                    this.OpenConnection();
                    DbDataAdapter insDbDataAdapter = insDbProviderFactory.CreateDataAdapter();
                    insDbDataAdapter.SelectCommand = CreateCommand(parCommandText, parDbParameters);
                    try
                    {
                        insDbDataAdapter.Fill(insDataTable);
                    }
                    catch (Exception ex)
                    {
                        this.CloseConnecion();
                        throw new SFDataException(GetErrorMessageMultilanguageString(7, new object[] { parCommandText, ex.Message }));
                    }

                    this.CloseConnecion();
                }
            
            return insDataTable;
        }

        public int ExecuteNonQuery(string parCommandText, DbParamCollection parDbParameters)
        {
            this.OpenConnection();
            int countOfRowsAffected;
            try
            {
                countOfRowsAffected = CreateCommand(parCommandText, parDbParameters).ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                this.CloseConnecion();
                throw new SFDataException(GetErrorMessageMultilanguageString(8, new object[] { parCommandText, ex.Message }));
            }

            this.CloseConnecion();
            return countOfRowsAffected;
        }

        public string ExecuteXmlReader(string parCommandText, DbParamCollection parDbParameters)
        {
            this.OpenConnection();
            string str = "";
            try
            {
                XmlReader xr;
                xr = ((System.Data.SqlClient.SqlCommand)CreateCommand(parCommandText, parDbParameters)).ExecuteXmlReader();
                while (xr.Read())
                {
                   str += xr.ReadOuterXml();
                }
                xr.Close();
            }
            catch (Exception ex)
            {
                this.CloseConnecion();
                throw new SFDataException(GetErrorMessageMultilanguageString(9, new object[] { parCommandText, ex.Message }));
            }

            this.CloseConnecion();
            return str;
        }

        public int ExecuteNonQuery(string parCommandText)
        {
            this.OpenConnection();
            int countOfRowsAffected;
            try
            {
                DbCommand cmd= CreateCommand(parCommandText, null);
                cmd.CommandType=CommandType.Text;
                countOfRowsAffected =cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                this.CloseConnecion();
                throw new SFDataException(GetErrorMessageMultilanguageString(8, new object[] { parCommandText, ex.Message }));
            }

            this.CloseConnecion();
            return countOfRowsAffected;
        }

        public object ExecuteScalar(string parCommandText, DbParamCollection parDbParameters)
        {
            this.OpenConnection();
            object value;
            try
            {
                value = CreateCommand(parCommandText, parDbParameters).ExecuteScalar();
            }
            catch (Exception ex)
            {
                this.CloseConnecion();
                throw new SFDataException(GetErrorMessageMultilanguageString(10, new object[] { parCommandText, ex.Message }));
            }

            this.CloseConnecion();
            return value;
        }

        public static string GetErrorMessageMultilanguageString(int Id, object[] parameters)
        {
            return String.Format(CurrentContext.ErrorMessagesMultilanguage[CurrentContext.LanguageId.ToString() + "|" + Id.ToString()].ToString(), parameters);
        }
    }
    public class DbParamCollection : CollectionBase, ICollection<DbParameter>
    {
        DbProviderFactory insDbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        #region ICollection<DbParameter> Members
        private int _OutputParameterIndex;
        public void Add(DbParameter item)
        {
            InnerList.Add(item);
        }
           
        public void Add(string parameterName, object parameterValue)
        {
            DbParameter insDbparameter = insDbProviderFactory.CreateParameter();
            insDbparameter.ParameterName = parameterName;
            if (parameterValue == null)
            {
                parameterValue = System.DBNull.Value;
            }
            insDbparameter.Value = parameterValue;
            InnerList.Add(insDbparameter);
        }

        public void AddOutput(string parameterName, DbType parDbType)
        {
            DbParameter insDbparameter = insDbProviderFactory.CreateParameter();
            insDbparameter.ParameterName = parameterName;
            insDbparameter.DbType = parDbType;
            insDbparameter.Direction = ParameterDirection.Output;
            _OutputParameterIndex = InnerList.Add(insDbparameter);
        }
        public DbParameter GetOutPutParameter()
        {
            return (DbParameter)InnerList[_OutputParameterIndex];
        }

        public bool Contains(DbParameter item)
        {
            return InnerList.Contains(item);
        }

        public void CopyTo(DbParameter[] array, int arrayIndex)
        {
            InnerList.CopyTo(array, arrayIndex);
        }

        public bool IsReadOnly
        {
            get
            {
                return InnerList.IsReadOnly;
            }
        }

        public bool Remove(DbParameter item)
        {
            InnerList.Remove(item);
            return true;
        }
        public Array ToArray()
        {
            return InnerList.ToArray();
        }

        #endregion

        #region IEnumerable<DbParameter> Members

        public new IEnumerator<DbParameter> GetEnumerator()
        {
            return (IEnumerator<DbParameter>)InnerList.GetEnumerator();
        }

        #endregion

    }
}
